-- PostgreSQL
--   database: jdbc:postgresql:db.cjprods.org:5432/rsms
--   username: rsms
--   password: rsms
--------------------------------------------------------------------------------
-- !!!!!!!! This user and database will be REMOVED after 2016-02-22 !!!!!!!!
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
---- DESIGN
-- User `rsms` is UNDERPRIVILEGED, and can ONLY select from functions.

--------------------------------------------------------------------------------
---- Error codes:
-- 'RSMS1': SOMETHING not found

--------------------------------------------------------------------------------
-- Transaction begin
BEGIN;

-- <editor-fold desc="reset database">
--------------------------------------------------------------------------------
-- reset database
--/*
DROP SCHEMA public CASCADE;
DROP ROLE rsms;
--*/

--------------------------------------------------------------------------------
-- Schema: public
CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
  IS 'standard public schema';
-- </editor-fold>

-- <editor-fold desc="types">
--------------------------------------------------------------------------------
-- Type: role
CREATE TYPE role AS ENUM (
  '信息部管理员',
  '销售部业务员', '销售部经理',
  '售后部信息员', '售后部业务员', '售后部经理'
);

--------------------------------------------------------------------------------
-- Type: result
CREATE TYPE result AS ENUM (
  '成功', '失败', '放弃'
);
-- </editor-fold>

-- <editor-fold desc="tables">
--------------------------------------------------------------------------------
-- <editor-fold desc="users">
-- Table: users
CREATE TABLE users
(
  id serial NOT NULL,
  seq serial NOT NULL,
  username text NOT NULL,
  credential text NULL, -- bcrypt salted-hashed password
  role role NOT NULL,
  name text NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_seq_unique UNIQUE (seq),
  CONSTRAINT users_username_unique UNIQUE (username)
);
COMMENT ON COLUMN users.credential IS 'bcrypt salted-hashed password';
--/*
-- testing data
INSERT INTO users(username, credential, role, name) VALUES
    ('0001', '$2a$10$kk/ig6XPqauJR0lsM64asegz.GylHcW9KZGgZ1xrq7IKRw/wkv9vW', '信息部管理员', '小兰'),
    ('0002', '$2a$10$p/OmDcXG7tVGtvl1CSMd2ekgnwbSVbLtIqA/Ii8ErjAjBJ1902kBu', '销售部经理', '小李'),
    ('0003', '$2a$10$uAayuv87WLnX6iYDDFbXMOWyBqypy4R10ikJwzRSNKtS8QiLvS9NG', '售后部信息员', '小艾'),
    ('0004', '$2a$10$TrKPCU1td/Cvz5HHGIQmiu4YP1BsvyMLMsz/NTjdrarCnkUNjoR2y', '售后部经理', '小曹'),
    ('0005', '$2a$10$wvDph2pa9oSvRiIJd8Th5.hrVst8HuMr.LV/0J54xzIKKmgpoNEo.', '售后部业务员', '小杨'),
    ('0006', '$2a$10$9a7E7dch8NL2YhyZviWQhOW9ofwwFCBHZVJ6tLlyRoraDgV6P5kLi', '售后部业务员', '老赵'),
    ('0007', '$2a$10$absr4cPC6.Jj.cPIVVn6X.1f/MGGhiZn32Oi92WBar.8YCYVIJNQK', '售后部业务员', '老王'),
    ('0008', '$2a$10$J2CUvuVp3P1bPSrJ9gl5XusZ564GvGo83ponps8gtOIehqr8s9sUO', '销售部业务员', '老李'),
    ('0009', '$2a$10$/pY8b0ppozWDMO6HWJ6H7uyY0ocP25Xwe2WLF/sBU2taqeil3wOFW', '销售部业务员', '老钱'),
    ('0010', '$2a$10$WsfLwBt8Bs5yut7f3se5Lu1cHlGFLNxQ5aUDmTebXWEAyurgWUlbi', '销售部业务员', '老郑'),
    ('0011', '$2a$10$P7VplmEhBa/2YKKFhGe5wepJ/XMk3VLqkUCLhnW2MEG5ruCLkY66O', '售后部业务员', '小周'),
    ('0012', '$2a$10$yMJu.hW3U04UQk6wxb5z/OGAvHmOpPG0avX1ky7oecc3jkbPHNa/K', '销售部业务员', '小何'),
    ('0013', '$2a$10$/6lvhO3POnGTo9cPKGpxr.MOqpIw5aX3D7ybWAsNnff5Fi2jlbMFC', '销售部业务员', '小卫'),
    ('0014', '$2a$10$lMlY4w.dRMegNlt5nnIAAOgtEGkLsIzHOUhvzPeGRp4.WBaEtupym', '销售部业务员', '小黄'),
    ('0015', '$2a$10$R2xermVKlSRw5r7wW/HTueo6l6I9bkmvTTqvz7MwlL8iu7qoOyD3m', '销售部经理', '老朱'),
    ('0016', '$2a$10$7HQyeJAfMG.f4ThG9H8yQOjHlFJdmrDNoCDdQxkcVY/MTlcuj141u', '销售部业务员', '老朱'),
    ('0017', '$2a$10$JMBG74zF2qdniDCMrhQZxew6ueBrJRQPkbzeiXpIH22ux8vdR2saO', '售后部经理', '小王'),
    ('0018', '$2a$10$osMpUltlSxWXC3wr0kcFCO4QtF3D0ouNg414jWnE0EfxJNFXYw0ba', '售后部业务员', '小明'),
    ('0019', '$2a$10$g20jIYkJbcuWRwcahrbnfeUL9xJ4SeXOJjqc8P4SLgK/a4JoSKxVa', '销售部业务员', '老孔'),
    ('0021', '$2a$10$1iwb7CD4amsjbxiJ9YZ1ueYbKYSF.uMqA/LfvWtyUJlqzTAHIQz.C', '售后部信息员', '小马'),
    ('0020', '$2a$10$8XsHawMBmFPJ43sjdPq2o..S6nmN.VCNAKs/hOT2.dxIL05RZCnou', '售后部信息员', '小红'),
    ('0022', '$2a$10$wEib1bDZaLxW1/Onvft7pekkn6NtTjX8JNwRCDWPlHXw5qYANrD6O', '售后部经理', '小陈');
--*/
-- </editor-fold>

--------------------------------------------------------------------------------
-- <editor-fold desc="sales">
-- Table: sales
CREATE TABLE sales
(
  id serial NOT NULL,
  applicant integer NOT NULL,
  approval text NULL,
  purpose text NOT NULL,
  budget numeric(10,2) NOT NULL,
  date timestamp NOT NULL,
  CONSTRAINT sales_pkey PRIMARY KEY (id),
  CONSTRAINT sales_applicant_fkey FOREIGN KEY (applicant) REFERENCES users
);
-- </editor-fold>

--------------------------------------------------------------------------------
-- <editor-fold desc="activitys">
-- Table: activitys
CREATE TABLE activitys
(
  id serial NOT NULL,
  sale integer NOT NULL,
  activity text NOT NULL,
  cost numeric(10,2) NOT NULL,
  date timestamp NOT NULL,
  CONSTRAINT activitys_pkey PRIMARY KEY (id),
  CONSTRAINT activitys_sale_fkey FOREIGN KEY (sale) REFERENCES sales
);
-- </editor-fold>

--------------------------------------------------------------------------------
-- <editor-fold desc="tickets">
-- Table: tickets
CREATE TABLE tickets
(
  id serial NOT NULL,
  complaint text NOT NULL,
  customer text NOT NULL,
  contact text NOT NULL,
  date timestamp NOT NULL,
  CONSTRAINT tickets_pkey PRIMARY KEY (id)
);
-- </editor-fold>

--------------------------------------------------------------------------------
-- <editor-fold desc="assignments">
-- Table: assignments
CREATE TABLE assignments
(
  id serial NOT NULL,
  ticket integer NOT NULL,
  assignee integer NOT NULL,
  result result NULL,
  detail text NULL,
  assign_date timestamp NOT NULL,
  finish_date timestamp NULL,
  CONSTRAINT assignments_pkey PRIMARY KEY (id),
  CONSTRAINT assignments_ticket_fkey FOREIGN KEY (ticket) REFERENCES tickets,
  CONSTRAINT assignments_assignee_fkey FOREIGN KEY (assignee) REFERENCES users,
  CONSTRAINT assignments_detail_check CHECK (
        ((result IS     NULL  OR result  = '成功') AND detail IS     NULL) OR
        ((result IS NOT NULL AND result != '成功') AND detail IS NOT NULL)),
  CONSTRAINT assignments_finish_date_check CHECK (
        (result IS     NULL AND finish_date IS     NULL) OR
        (result IS NOT NULL AND finish_date IS NOT NULL))
);
-- </editor-fold>

--------------------------------------------------------------------------------
-- <editor-fold desc="verbose_tickets">
-- View: verbose_tickets
CREATE VIEW verbose_tickets AS
 SELECT tickets.id AS ticket,
    assignments.id AS assignment,
    assignments.assignee,
    tickets.complaint,
    tickets.customer,
    tickets.contact,
        CASE
            WHEN assignments.assignee IS NULL THEN NULL
            ELSE format('%s(%s)', users.name, users.username)
        END AS assignee_name,
    assignments.result,
    assignments.detail
   FROM tickets
     LEFT JOIN assignments ON assignments.ticket = tickets.id
     LEFT JOIN users ON assignments.assignee = users.id;
-- </editor-fold>

-- </editor-fold>

-- <editor-fold desc="functions">
-- <editor-fold desc="users">
--------------------------------------------------------------------------------
-- Function: () -> ()
CREATE FUNCTION bootstrap()
  RETURNS void AS
$BODY$
BEGIN
    -- user admin, password admin
    INSERT INTO users(username, credential, role, name) VALUES
            ('admin', '$2a$10$qpEAwMIZn.upbjnhcZ4cFO8EUcA8MiwOlLEwmBxcW9AWQ4Km9nM.2', '信息部管理员', '管理员');
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text, text, role, text) -> ()
CREATE FUNCTION add_user(username_ text, credential_ text, role_ text, name_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO users(username, credential, role, name)
        VALUES (username_, credential_, role_::role, name_);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION revoke_user(user_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET seq = default WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION leave_user(user_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET credential = NULL WHERE credential IS NOT NULL AND id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'RSMS1'; END IF;
    PERFORM revoke_user(user_);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text) -> (integer, text, role, text)
CREATE FUNCTION user_info(username_ text)
  RETURNS TABLE(seq_ integer, credential_ text, role_ role, name_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT seq, credential, role, name
          FROM users WHERE credential IS NOT NULL AND username = username_;
    IF not found THEN raise 'no such username' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer)
CREATE FUNCTION user_from_seq(seq_ text)
  RETURNS TABLE(user_ integer) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id FROM users WHERE credential IS NOT NULL AND seq = seq_::integer;
    IF not found THEN raise 'no such seq' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer, text, role, text)[0..10]
CREATE FUNCTION list_users(after_ text)
  RETURNS TABLE(user_ integer, username_ text, role_ role, name_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, username, role, name FROM users
          WHERE credential IS NOT NULL AND (after_::integer < 0 OR id < after_::integer)
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_password(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM revoke_user(user_);
    UPDATE users SET credential = new_ WHERE credential IS NOT NULL AND id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, role) -> ()
CREATE FUNCTION modify_role(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM revoke_user(user_);
    UPDATE users SET role = new_::role WHERE credential IS NOT NULL AND id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_name(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET name = new_ WHERE credential IS NOT NULL AND id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>

-- <editor-fold desc="sales">
--------------------------------------------------------------------------------
-- Function: (integer, text, numeric(10,2)) -> ()
CREATE FUNCTION add_sale(user_ text, purpose_ text, budget_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO sales(applicant, purpose, budget, date)
        VALUES (user_::integer, purpose_, budget_::numeric(10,2), now());
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION cancel_sale(user_ text, sale_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE sales SET approval = '取消' WHERE id = sale_::integer AND approval IS NULL AND applicant = user_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION approve_sale(sale_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE sales SET approval = '' WHERE id = sale_::integer AND approval IS NULL;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION reject_sale(sale_ text, reason_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE sales SET approval = reason_ WHERE id = sale_::integer AND approval IS NULL;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer, text, text, text, text, text, text)[0..10]
CREATE FUNCTION list_sales(after_ text)
  RETURNS TABLE(sale_ integer, applicant_ text, approval_ text, purpose_ text, budget_ text, date_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT sales.id,
                    CASE WHEN applicant IS NULL THEN NULL
                         ELSE format('%s(%s)', name, username)
                    END,
                approval, purpose, budget::text, date::text
          FROM sales JOIN users ON sales.applicant = users.id
          WHERE after_::integer < 0 OR sales.id < after_::integer
          ORDER BY sales.id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> (integer, text, text, text, text, text)[0..10]
CREATE FUNCTION list_my_sales(after_ text, user_ text)
  RETURNS TABLE(sale_ integer, approval_ text, purpose_ text, budget_ text, date_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, approval, purpose, budget::text, date::text FROM sales
          WHERE applicant = user_::integer AND (after_::integer < 0 OR id < after_::integer)
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION user_owns_sale(user_ text, sale_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM id FROM sales WHERE applicant = user_::integer AND id = sale_::integer;
    IF not found THEN raise 'no such sale' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, text, numeric(10,2)) -> ()
CREATE FUNCTION add_activity(user_ text, sale_ text, activity_ text, cost_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM user_owns_sale(user_, sale_);
    INSERT INTO activitys(sale, activity, cost, date)
        VALUES (sale_::integer, activity_, cost_::numeric(10,2), now());
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> (integer, text, text, text, text, text)[0..10]
CREATE FUNCTION list_activitys(after_ text, sale_ text)
  RETURNS TABLE(id_ integer, activity_ text, cost_ text, date_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, activity, cost::text, date::text FROM activitys
          WHERE sale = sale_::integer AND (after_::integer < 0 OR id < after_::integer)
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, integer) -> (integer, text, text, text, text, text)[0..10]
CREATE FUNCTION list_my_activitys(after_ text, user_ text, sale_ text)
  RETURNS TABLE(id_ integer, activity_ text, cost text, date_ text) AS
$BODY$
BEGIN
    PERFORM user_owns_sale(user_, sale_);
    RETURN QUERY SELECT * FROM list_activitys(after_, sale_);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>

-- <editor-fold desc="tickets">
--------------------------------------------------------------------------------
-- Function: (text, text, text) -> ()
CREATE FUNCTION add_ticket(complaint_ text, customer_ text, contact_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO tickets(complaint, customer, contact, date)
        VALUES (complaint_, customer_, contact_, now());
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION assign_ticket(ticket_ text, user_ text)
  RETURNS void AS
$BODY$
BEGIN
    -- check user availability
    PERFORM * FROM list_busy_assignees() WHERE assignee_::integer = user_::integer;
    IF found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;

    -- check user role
    PERFORM id FROM users WHERE credential IS NOT NULL AND id = user_::integer AND role = '售后部业务员';
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;

    -- check tickets is assigned or not
    PERFORM ticket FROM (
            SELECT DISTINCT ON (ticket) ticket, assignee, result
              FROM verbose_tickets
              ORDER BY ticket DESC, assignment DESC
        ) AS latest
        WHERE (assignee IS NULL OR result != '成功') AND ticket = ticket_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;

    INSERT INTO assignments(ticket, assignee, assign_date)
        VALUES (ticket_::integer, user_::integer, now());
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION done_assignment(assignment_ text, user_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE assignments SET result = '成功', finish_date = now()
          WHERE result IS NULL AND id = assignment_::integer AND assignee = user_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, text) -> ()
CREATE FUNCTION fail_assignment(assignment_ text, user_ text, detail_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE assignments SET result = '失败', detail = detail_, finish_date = now()
          WHERE result IS NULL AND id = assignment_::integer AND assignee = user_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, text) -> ()
CREATE FUNCTION waive_assignment(assignment_ text, user_ text, detail_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE assignments SET result = '放弃', detail = detail_, finish_date = now()
          WHERE result IS NULL AND id = assignment_::integer AND assignee = user_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'RSMS1'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer, text, text, text, result, text)[0..10]
CREATE FUNCTION list_tickets(after_ text)
  RETURNS TABLE(assignment_ integer, complaint_ text, customer_ text, assignee_ text, result_ result, detail_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT assignment, complaint, customer, assignee_name, result, detail
          FROM verbose_tickets
          WHERE after_::integer < 0 OR assignment < after_::integer
          ORDER BY assignment DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> (integer, text, text, text, result, text)[0..10]
CREATE FUNCTION list_my_tickets(after_ text, user_ text)
  RETURNS TABLE(assignment_ integer, complaint_ text, customer_ text, contact_ text, result_ result, detail_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT assignment, complaint, customer, contact, result, detail
          FROM verbose_tickets
          WHERE assignee = user_::integer
                 AND (after_::integer < 0 OR assignment < after_::integer)
          ORDER BY assignment DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer, text, text, result, text)[0..10]
CREATE FUNCTION list_latest_tickets(after_ text)
  RETURNS TABLE(ticket_ integer, complaint_ text, customer_ text, result_ result, detail_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT ticket, complaint, customer, result, detail FROM (
            SELECT DISTINCT ON (ticket) ticket, complaint, customer, result, detail, assignee
              FROM verbose_tickets
              ORDER BY ticket DESC, assignment DESC
        ) AS latest
        WHERE (assignee IS NULL OR result != '成功') AND (after_::integer < 0 OR ticket < after_::integer) LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: () -> (integer)[*]
CREATE FUNCTION list_busy_assignees()
  RETURNS TABLE(assignee_ integer) AS
$BODY$
BEGIN
    RETURN QUERY SELECT assignee FROM (
            SELECT DISTINCT ON (ticket) assignee, result
              FROM verbose_tickets
              ORDER BY ticket DESC, assignment DESC
        ) AS latest WHERE assignee IS NOT NULL AND result IS NULL;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer, text)[0..10]
CREATE FUNCTION list_free_assignees(after_ text)
  RETURNS TABLE(assignee_ integer, name_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, format('%s(%s)', name, username) FROM users
          WHERE credential IS NOT NULL AND role = '售后部业务员'
                  AND (after_::integer < 0 OR id < after_::integer)
                  AND id NOT IN (SELECT * FROM list_busy_assignees())
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>
-- </editor-fold>

-- <editor-fold desc="roles">
--------------------------------------------------------------------------------
-- Role: rsms
CREATE ROLE rsms LOGIN
  ENCRYPTED PASSWORD 'md50fc7849b96bd82d93c7f8b2b21bbc293'  -- md5 for 'rsms'
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION
  CONNECTION LIMIT 10;
-- </editor-fold>

--------------------------------------------------------------------------------
-- Bootstrap
SELECT * FROM bootstrap();

--------------------------------------------------------------------------------
-- Transaction end
COMMIT;

